package com.elitel.common.access;

import com.elitel.frame.main.entity.AcServiceFilterField;
import com.elitel.frame.main.entity.access.FilterSqlHandlerDTO;
import com.elitel.frame.main.entity.constant.DictionaryConstant;
import com.elitel.frame.main.entity.enums.FilterTypeEnum;
import org.springframework.util.ObjectUtils;

import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 过滤站点数据SQL处理
 * @author hi_bj
 */
public class FilterStationDataSqlHandler {

    /**
     * 处理 PrecompileSql
     *
     * @param sql 预执行SQL
     * @param filterSqlHandler SQL过滤处理对象
     * @param databaseType
     * @return
     */
    public static String doFilterSql(String sql, FilterSqlHandlerDTO filterSqlHandler, String databaseType) {
        if (filterSqlHandler == null || (!filterSqlHandler.getFilterStationFlag() && !filterSqlHandler.getFilterTimeFlag())) {
            return sql;
        }
        List<AcServiceFilterField> acServiceFilterFieldList = filterSqlHandler.getFilterFiledList();
        Map<String, List<AcServiceFilterField>> acServiceFilterFieldListMap = acServiceFilterFieldList.stream()
                .collect(Collectors.groupingBy(AcServiceFilterField::getFilterType));
        //处理测站和数据过滤SQL
        int index = sql.lastIndexOf(") a");
        StringBuilder sb = new StringBuilder(sql.substring(0, index + 3));

        //1.拼接站点过滤
        List<AcServiceFilterField> stationList = acServiceFilterFieldListMap.get(FilterTypeEnum.STCD.name());
        if (filterSqlHandler.getFilterStationFlag()) {
            if (!ObjectUtils.isEmpty(stationList)) {
                String appCode = filterSqlHandler.getAppCode();
                String serviceId = filterSqlHandler.getServiceId();
                sb.append(" INNER JOIN AC_FILTER_STATION B ON B.APP_CODE = '").append(appCode).append("' ");
                sb.append(" AND B.SERVICE_ID = '").append(serviceId).append("' ");
                stationList.forEach(item -> {
                    sb.append(" AND A.").append(item.getFieldCode()).append(" = B.STCD ");
                });
            } else {
                sb.append(" WHERE 1=2 ");
            }
        }

        //2.拼接时间过滤
        List<AcServiceFilterField> timeList = acServiceFilterFieldListMap.get(FilterTypeEnum.TM.name());
        if (filterSqlHandler.getFilterTimeFlag()) {
            if (!ObjectUtils.isEmpty(timeList)) {
                //如果没有过滤测站，时间过滤需拼接where，否则， 继续拼接inner join 的连接条件
                if (!filterSqlHandler.getFilterStationFlag()) {
                    sb.append(" WHERE 1=1 ");
                }
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                timeList.forEach(item -> {
                    if (!ObjectUtils.isEmpty(filterSqlHandler.getFilterStartTime())) {
                        String startTime = adaptDatabaseDate(sdf.format(filterSqlHandler.getFilterStartTime())
                                , item.getFieldType()
                                , databaseType);
                        //拼接时间过滤
                        sb.append(" AND A.").append(item.getFieldCode()).append(" >= ").append(startTime);
                    }
                    if (!ObjectUtils.isEmpty(filterSqlHandler.getFilterEnterTime())) {
                        String endTime = adaptDatabaseDate(sdf.format(filterSqlHandler.getFilterEnterTime())
                                , item.getFieldType()
                                , databaseType);
                        sb.append(" AND A.").append(item.getFieldCode()).append(" <=").append(endTime);
                    }
                });
            } else {
                if (!filterSqlHandler.getFilterStationFlag()) {
                    sb.append(" WHERE 1=2 ");
                }
            }
        }
        return sb.append(sql.substring(index + 3)).toString();
    }

    /**
     * 适配数据库日期
     * @param value
     * @param fieldType
     * @param databaseType
     * @return
     */
    private static String adaptDatabaseDate(String value, String fieldType, String databaseType) {
        if (ObjectUtils.isEmpty(fieldType) || ObjectUtils.isEmpty(databaseType)) {
            return "'" + value + "'";
        }
        //如果数据库类型包含日期时间特殊字符，需特殊处理
        if (fieldType.toLowerCase().contains("time") || fieldType.toLowerCase().contains("date")) {
            if (DictionaryConstant.DatabaseType_ORACLE.equals(databaseType)) {
                return " to_date('" + value + "','yyyy-mm-dd hh24:mi:ss') ";
            }
        }
        return "'" + value + "'";
    }
}
